﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Reflection;
using System.Configuration;
using System.Collections.Specialized;

namespace Tools.SqlServer
{
    public sealed partial class SqlHelper
    {
        public static int CommonTimeOut = 0;

        //public static SqlConnection _connection;
        //public static SqlTransaction _transaction;

        //public static SqlTransaction Trans
        //{
        //    get { return _transaction; }
        //}

        //public static SqlTransaction BeginTransaction()
        //{
        //    if (_connection == null)
        //    {
        //        _connection = GetConnection();
        //    }

        //    if (_connection.State != ConnectionState.Open)
        //    {
        //        _connection.Open();
        //    }
        //    if (_transaction == null)
        //    {
        //        _transaction = _connection.BeginTransaction();
        //    }
        //    return _transaction;
        //}

        //public static void RollBackTransaction()
        //{
        //    _transaction.Rollback();
        //}
        //public static void CommitTransaction()
        //{
        //    _transaction.Commit();
        //}


        //public static void EndTransaction()
        //{
        //    if (_transaction != null)
        //    {
        //        _transaction.Dispose();
        //        _transaction = null;
        //    }
        //    if (_connection != null)
        //    {
        //        _connection.Close();
        //        _connection.Dispose();
        //        _connection = null;
        //    }
        //}

        public static bool CommitSomeSql(List<string> sql)
        {

            bool reVal = false;
            SqlTranHelper sqlTranHelper = new SqlTranHelper();
            try
            {
                sqlTranHelper.BeginTransaction();
                foreach (string s in sql)
                {
                    int count = ExecuteNonQuery(s, sqlTranHelper.Trans);
                }

                sqlTranHelper.CommitTransaction();
            }
            catch
            {
                sqlTranHelper.RollBackTransaction();
                reVal = false;
            }
            finally
            {
                sqlTranHelper.EndTransaction();
            }

            return reVal;
        }

        public static string Transaction(Action<System.Data.SqlClient.SqlTransaction> op)
        {
            string Message = "Error!";

            SqlTranHelper sqlTranHelper = new SqlTranHelper();
            try
            {
                sqlTranHelper.BeginTransaction();
                op(sqlTranHelper.Trans);
                sqlTranHelper.CommitTransaction();
                Message = "";
            }
            catch (Exception ex)
            {
                sqlTranHelper.RollBackTransaction();
                if (!string.IsNullOrEmpty(ex.Message))
                {
                    Message = ex.Message;
                }
            }
            finally
            {
                sqlTranHelper.EndTransaction();
            }
            return Message;
        }

        public static DataSet ExecuteDataset(string commandText, SqlTransaction dbt = null, int TimeOut = 0)
        {
            //return ExecuteDataset(SqlHelper.GetConnection(), CommandType.Text, commandText);
            CommonTimeOut = TimeOut;
            DataSet ds;
            if (dbt != null)
            {
                ds = ExecuteDataset(dbt, CommandType.Text, commandText);
            }
            else
            {
                ds = ExecuteDataset(SqlHelper.GetConnection(), CommandType.Text, commandText);
            }
            CommonTimeOut = 0;
            return ds;
        }

        //public static DataSet ExecuteDataset(string commandText, params SqlParameter[] commandParameters)
        //{
        //    return ExecuteDataset(SqlHelper.GetConnection(), CommandType.Text, commandText, commandParameters);
        //}
        public static DataSet ExecuteDataset(string commandText, SqlParameter[] commandParameters, SqlTransaction dbt = null)
        {
            if (dbt != null)
            {
                return ExecuteDataset(dbt, CommandType.Text, commandText, commandParameters);
            }
            else
            {
                return ExecuteDataset(SqlHelper.GetConnection(), CommandType.Text, commandText, commandParameters);
            }
        }

        public static int ExecuteNonQuery(string commandText, params SqlParameter[] commandParameters)
        {
            return ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, commandText, commandParameters);
        }
        public static int ExecuteNonQuery(string commandText, SqlTransaction dbt = null, int TimeOut = 0)
        {
            CommonTimeOut = TimeOut;
            int iVal;
            if (dbt != null)
            {
                iVal = ExecuteNonQuery(dbt, CommandType.Text, commandText);
            }
            else
            {
                iVal = ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, commandText);
            }
            CommonTimeOut = 0;
            return iVal;
        }
        public static object ExecuteScalar(string commandText, SqlTransaction dbt = null)
        {
            // 执行参数为空的方法 
            //return ExecuteScalar(SqlHelper.GetConnection(), CommandType.Text, commandText);
            if (dbt != null)
            {
                return ExecuteScalar(dbt, CommandType.Text, commandText);
            }
            else
            {
                return ExecuteScalar(SqlHelper.GetConnection(), CommandType.Text, commandText);
            }
        }
        public static object ExecuteScalar(string commandText, params SqlParameter[] commandParameters)
        {
            // 执行参数为空的方法 
            return ExecuteScalar(SqlHelper.GetConnection(), CommandType.Text, commandText, commandParameters);
        }

        /// <summary>
        /// 通用的entity类转化为SqlParameter[] ，使用反射
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static SqlParameter[] getParameter(object model)
        {

            List<SqlParameter> param = new List<SqlParameter>();

            PropertyInfo[] properties = model.GetType().GetProperties();
            foreach (var p in properties)
            {
                string key = p.Name;
                object value = p.GetValue(model, null);
                SqlParameter parameter = new SqlParameter(key, value);

                param.Add(parameter);
            }
            return param.ToArray();
        }

        public static SqlParameter getParameter(string key, object value)
        {
            SqlParameter paras = new SqlParameter(key, value);
            return paras;
        }

        public static SqlParameter[] getParameterWithDic(Dictionary<string, object> dic)
        {
            List<SqlParameter> lstSqlParameter = new List<SqlParameter>();
            foreach (var d in dic)
            {
                lstSqlParameter.Add(getParameter(d.Key, d.Value));
            }
            return lstSqlParameter.ToArray();
        }

        public static bool updateWithBulkCopy(DataTable dt, string datetable)
        {
            bool reVal = false;
            SqlBulkCopy sqlbulkcopy = null;

            try
            {

                sqlbulkcopy = new SqlBulkCopy(SqlHelper.GetConnSting(), SqlBulkCopyOptions.UseInternalTransaction);

                sqlbulkcopy.DestinationTableName = datetable;
                sqlbulkcopy.BulkCopyTimeout = 1000;
                sqlbulkcopy.WriteToServer(dt);

                reVal = true;
            }
            catch (Exception ex)
            {
                //strErr = ex.Message;
                reVal = false;
            }
            finally
            {
                sqlbulkcopy.Close();

            }
            return reVal;
        }
        public static bool updateWithBulkCopy(DataTable dt, string datetable, SqlTransaction dbt)
        {
            bool reVal = false;
            SqlBulkCopy sqlbulkcopy = null;

            try
            {

                sqlbulkcopy = new SqlBulkCopy(dbt.Connection, SqlBulkCopyOptions.CheckConstraints, dbt);
                sqlbulkcopy.DestinationTableName = datetable;
                sqlbulkcopy.BulkCopyTimeout = 1000;
                sqlbulkcopy.WriteToServer(dt);

                reVal = true;
            }
            finally
            {
                sqlbulkcopy.Close();

            }
            return reVal;
        }

        public static object StringNullParam(string value)
        {
            if (string.IsNullOrEmpty(value))
            {
                return DBNull.Value;
            }
            else
            {
                return value;
            }
        }

        public static DataSet sqlToDataSet(string sql, SqlParameter[] parameters, SqlTransaction dbt = null)
        {
            DataSet ds;
            if (dbt == null)
            {
                ds = SqlHelper.ExecuteDataset(SqlHelper.GetConnection(), CommandType.Text, sql, parameters);
            }
            else
            {
                ds = SqlHelper.ExecuteDataset(dbt, CommandType.Text, sql, parameters);
            }
            return ds;
        }

        public static DataSet sqlToDataSet(string sql, List<SqlParameter> lstParam, SqlTransaction dbt = null)
        {
            DataSet ds = sqlToDataSet(sql, lstParam.ToArray(), dbt);
            return ds;
        }

        public static bool sqlToExecute(string sql, SqlParameter[] parameters, SqlTransaction dbt = null)
        {
            bool reVal = false;
            if (dbt == null)
            {
                reVal = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql, parameters) > 0;
            }
            else
            {
                reVal = SqlHelper.ExecuteNonQuery(dbt, CommandType.Text, sql, parameters) > 0;
            }
            return reVal;
        }
        //public static SqlDbType getSqlType(object type)
        //{
        //    if (type is string)
        //    {
        //        return SqlDbType.NVarChar;
        //    }
        //    else if (type is int)
        //    {
        //        return SqlDbType.Int;
        //    }
        //    else if (type is double)
        //    {
        //        return SqlDbType.Decimal;
        //    }
        //    else
        //    {
        //        return SqlDbType.VarChar;
        //    }
        //}

        //public static SqlParameter[] getParameter(NameValueCollection nv,string keySet,string DbType)
        //{
        //    //SqlParameter[] param = new SqlParameter[key.Count];
        //    List<SqlParameter> param = new List<SqlParameter>();
        //    string[] keys = nv.AllKeys;
        //    for (int i = 0; i < keys.Length; i++)
        //    {
        //        if (!string.IsNullOrEmpty(keySet))
        //        {
        //            if (!keySet.Contains(keys[i]))
        //            {
        //                continue;
        //            }
        //        }
        //        string value =  nv[keys[i]].Trim();
        //        object Ovalue;
        //        if (DbType.Equals("int"))
        //        {

        //            Ovalue = Convert.ToInt32(value);
        //        }

        //        string key = "@"+keys[i];
        //        SqlParameter p = new SqlParameter(key, value);

        //        param.Add(p);
        //    }

        //    return param.ToArray();
        //}





        //private SqlDbType getDbType(string DbType)
        //{
        //    SqlDbType dbType = SqlDbType.NVarChar;
        //    switch (DbType)
        //    {


        //        case "int":
        //            dbType = SqlDbType.Int;
        //            break;
        //        case "decimal":
        //            dbType = SqlDbType.Decimal;
        //            break;

        //    }

        //    return dbType; 
        //}

    }
}
